﻿Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Imports System.IO
Public Class borrower
    Dim con As New MySqlConnection
    Dim result As Integer
    Dim cmd As New MySqlCommand
    Dim da As New MySqlDataAdapter
    Dim publictable As New DataTable
    Dim dt As New DataTable

    Private Sub manage_user_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As MySqlConnection = New MySqlConnection("server=localhost;userid=root;database=vb-librarysys")
        Try

            con.Open()

            dt = New DataTable

            With (cmd)
                .Connection = con
                .CommandText = "Select * from borrower"

            End With
            da.SelectCommand = cmd
            da.Fill(dt)

            dtguser.DataSource = dt
            Dim r As DataRow
            TextBox1.AutoCompleteCustomSource.Clear()

            For Each r In dt.Rows
                TextBox1.AutoCompleteCustomSource.Add(r.Item("borrower_id").ToString)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()

        con.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        con.ConnectionString = ("server=localhost;user id=root;password=;database=vb-librarysys")
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = "UPDATE  `vb-librarysys`.`borrower` SET  `name` =  '" & txtname.Text & "',`contactNo.` =  '" & txtcontact.Text & "',`emailadd` =  '" & txtemail.Text & "',`address` =  '" & txtaddress.Text & "',`issueBook` =  '" & txtIssue.Text & "',`returnBook` =  '" & txtreturn.Text & "',`reIssue` =  '" & txtreIssue.Text & "',`reserve` =  '" & txtreserve.Text & "',`date` =  '" & txtdate.Text & "' WHERE`borrower`.`borrower_id` =" & Val(lblid.Text) & ";"
                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    MsgBox("Data has been Updated!")
                Else
                    MsgBox("Successfully Updated")
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        con.Close()
        Call btnRead_Click(sender, e)
    End Sub


    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dtguser.CellContentClick

    End Sub

    Private Sub btncreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncreate.Click
        con.ConnectionString = ("server=localhost;user id=root;password=;database=vb-librarysys")
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = "INSERT INTO `vb-librarysys`.`borrower` (`borrower_id`, `name`, `contactNo.`, `emailadd`, `address`, `issueBook`, `returnBook`, `reIssue`, `reserve`, `date`) " & _
     "VALUES (NULL, '" & txtname.Text & "', '" & txtcontact.Text &
"', '" & txtemail.Text & "','" & txtaddress.Text & "','" & txtIssue.Text & "','" & txtreturn.Text & "','" & txtreIssue.Text & "','" & txtreserve.Text & "','" & txtdate.Text & "');"

                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    MsgBox("Data has been Inserted!")
                Else
                    MsgBox("Succesfully Save!")
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub

    Public Sub filltable(ByVal dtgrd As Object)
        Dim publictable As New DataTable
        Try

            da.SelectCommand = cmd
            da.Fill(publictable)
            dtgrd.DataSource = publictable
            dtgrd.Columns(0).Visible = False
            da.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

    Private Sub btnRead_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MDIchild1.Click
        con.ConnectionString = ("server=localhost;user id=root;password=;database=vb-librarysys")
        Try
            con.Open()
            With (cmd)
                .Connection = con
                .CommandText = "Select * from borrower"
            End With

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        con.Close()
        da.Dispose()
        filltable(dtguser)
    End Sub
    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        con.ConnectionString = ("server=localhost;user id=root;password=;database=vb-librarysys")
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = "DELETE FROM `vb-librarysys`.`borrower` WHERE `borrower`.`borrower_id` = " & Val(lblid.Text) & ";"
                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    MsgBox("Data has been Updated!")
                Else
                    MsgBox("Successfully Updated")
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
        con.Close()
        Call btnRead_Click(sender, e)
    End Sub

    Private Sub dtguser_CellMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dtguser.CellMouseClick
        lblid.Text = dtguser.CurrentRow.Cells(0).Value
        txtname.Text = dtguser.CurrentRow.Cells(1).Value
        txtcontact.Text = dtguser.CurrentRow.Cells(2).Value
        txtemail.Text = dtguser.CurrentRow.Cells(3).Value
        txtaddress.Text = dtguser.CurrentRow.Cells(4).Value
        txtIssue.Text = dtguser.CurrentRow.Cells(5).Value
        txtreturn.Text = dtguser.CurrentRow.Cells(6).Value
        txtreIssue.Text = dtguser.CurrentRow.Cells(7).Value
        txtreserve.Text = dtguser.CurrentRow.Cells(8).Value
        txtdate.Text = dtguser.CurrentRow.Cells(9).Value

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        txtname.Clear()
        txtcontact.Clear()
        txtemail.Clear()
        txtaddress.Clear()
        txtIssue.Clear()
        txtreturn.Clear()
        txtreIssue.Clear()
        txtreserve.Clear()
        txtdate.Clear()
        dt.Clear()
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Dim con As MySqlConnection = New MySqlConnection("server=localhost;userid=root;database=vb-librarysys")
        Try

            con.Open()
            dt = New DataTable

            With (cmd)
                .Connection = con
                .CommandText = "Select * from borrower where borrower_id like '" & TextBox1.Text & "%'"

            End With

            da.SelectCommand = cmd
            da.Fill(dt)

            dtguser.DataSource = dt

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()

        con.Close()
    End Sub


    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Dim dlg As New SaveFileDialog
        dlg.DefaultExt = "*.accdb"
        dlg.Filter = "ACCESS 2007|*.accdb|All File|*"
        If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then
            con.Close()
            File.Copy(Application.StartupPath & "\db.accdb", dlg.FileName, True)
            con.Open()
        End If
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Dim dlg As New OpenFileDialog
        dlg.DefaultExt = "*.accdb"
        dlg.Filter = "ACCESS 2007|*.accdb|All File|*"
        If dlg.ShowDialog = Windows.Forms.DialogResult.OK Then
            con.Close()
            File.Copy(dlg.FileName, Application.StartupPath & "\db.accdb", True)
            manage_user_Load(Nothing, Nothing)
        End If
    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        book.Show()
    End Sub
End Class
